import re


# 为其他数据类型添加注释
def add_todo_comment(match):
    column_type = match.group(1)
    if 'text' not in column_type:
        return f"{column_type}"
    return column_type
def convert_ddl(oracle_ddl):
    # 将表名前加上public.
    oracle_ddl = re.sub(r'CREATE TABLE (\w+)', r'CREATE TABLE public.\1', oracle_ddl, flags=re.IGNORECASE)

    # 转换VARCHAR2或VARCHAR为text
    oracle_ddl = re.sub(r'\b(VARCHAR2|VARCHAR)\b\s*\(\d+\)', 'text', oracle_ddl, flags=re.IGNORECASE)



    oracle_ddl = re.sub(r'(\b\w+\b)(?!\s*text\b)', add_todo_comment, oracle_ddl, flags=re.IGNORECASE)

    # 更新COMMENT ON语句中的列引用
    oracle_ddl = re.sub(r'COMMENT ON COLUMN (\w+)\.(\w+)', r'COMMENT ON COLUMN public.\1.\2', oracle_ddl, flags=re.IGNORECASE)

    return oracle_ddl

# 原始Oracle DDL
oracle_sql = """
CREATE TABLE XX (
  id NUMBER,
  name VARCHAR2(255),
  description VARCHAR(4000),
  date_created DATE
) 
COMMENT ON COLUMN XX.id IS 'ID of the record'
COMMENT ON COLUMN XX.name IS 'Name of the record'
COMMENT ON COLUMN XX.description IS 'Description of the record'
"""

# 转换后的DDL
converted_ddl = convert_ddl(oracle_sql)

# 打印转换后的DDL
print(converted_ddl)